package hn.cch.framework.util;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

public class JdbcUtil {
    private static Logger logger = LoggerFactory.getLogger(JdbcUtil.class);


    public void mysqlJdbc() {
        String driver = "oracle.jdbc.driver.OracleDriver";
        String url = "jdbc:oracle:thin:@192.168.1.25:1521/orcl19";
        String user = "rz_hrxt";
        String password = "pLjyU0VmLEqVOhMd";


        Connection connection = null;
        Statement statement = null;

        PreparedStatement preparedStatement = null;

        CallableStatement callableStatement = null;


        ResultSet rs = null;

        try {

            Class.forName(driver);
            connection = DriverManager.getConnection(url, user, password);


            preparedStatement = connection.prepareStatement("");


            callableStatement = connection.prepareCall("{ call testc(?) }");
            callableStatement.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR);
            callableStatement.execute();

        } catch (Exception e) {
            logger.error("oracleJdbc:{}", e.getMessage());
        } finally {
            try {
                if (statement.isClosed()) {
                    statement.close();
                }

                if (connection.isClosed()) {
                    connection.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }


    }

    public static void oracleJdbc() {
        String driver = "oracle.jdbc.driver.OracleDriver";
        String url = "jdbc:oracle:thin:@192.168.10.134:1521:orcl";
        String user = "chench";
        String password = "chench";

        Connection connection = null;
        CallableStatement callableStatement = null;

        try {
            Class.forName(driver);
            connection = DriverManager.getConnection(url, user, password);

            callableStatement = connection.prepareCall("{ call sp_user(?,?) }");
            callableStatement.setString(1, "");
            callableStatement.registerOutParameter(2, oracle.jdbc.OracleTypes.CURSOR);

            callableStatement.execute();
            Object object = callableStatement.getObject(2);
            if (object instanceof ResultSet) {
                ResultSet resultSet = (ResultSet) object;
                ResultSetMetaData metaData = resultSet.getMetaData();
                int columnCount = metaData.getColumnCount();
                while (resultSet.next()) {
                    for (int i = 1; i <= columnCount; i++) {
                        //使用ResultSetMetaData.getColumnLabel(index)获取的列名是大写的
                        logger.info("{}:{}", metaData.getColumnLabel(i), resultSet.getObject(i));
                    }
                    //使用ResultSet.getObject() 传入的列名是不区分大小写
                    logger.info("{}:{}", resultSet.getObject("user_code"), resultSet.getObject("user_name"));
                    logger.info("{}:{}", resultSet.getObject("USER_CODE"), resultSet.getObject("USER_NAME"));
                }
            }


        } catch (Exception e) {
            logger.error("oracleJdbc:{}", e.getMessage());
        } finally {
            try {
                if (callableStatement.isClosed()) {
                    callableStatement.close();
                }

                if (connection.isClosed()) {
                    connection.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }

    }


    public void mssqlJdbc() {
        System.out.println();
    }


}
